package service;

import java.util.List;

import bean.FirstCategory;
import bean.FirstCategoryRowMapper;
import bean.Goods;
import bean.GoodsRowMapper;
import bean.SecondCategory;
import bean.SecondCategoryRowMapper;
import bean.TcImg;
import bean.TcImgRowMapper;
import bean.ThirdCategory;
import bean.ThirdCategoryRowMapper;
import common.BaseDAO;

public class GoodsDAO extends BaseDAO{
	//根据搜索词在商品名/关键字/中查询
	public List<ThirdCategory> queryBySearchWord(String searchWord,int sortWay,int pagesize,int viewPage){
		int offset=pagesize*(viewPage-1);
		String sql;
		switch(sortWay) {
			case 1://上架时间升序
				sql="select * from third_category "
						+ "where second_categoryid in "
						+ "(select second_categoryid from second_category"
						+ " where first_categoryid in"
						+ "(select first_categoryid from first_category "
						+ "where locate(?,name)>0)) "
						+ "or second_categoryid in "
						+ "(select second_categoryid from second_category "
						+ "where locate(?,name)>0) "
						+ "or locate(?,name)>0 or locate(?,keyword)>0 "
						+ "group by third_categoryid "
						+ "order by onsale_time limit ?,?";
				break;
			case 2://上架时间降序
				sql="select * from third_category "
						+ "where second_categoryid in "
						+ "(select second_categoryid from second_category"
						+ " where first_categoryid in"
						+ "(select first_categoryid from first_category "
						+ "where locate(?,name)>0)) "
						+ "or second_categoryid in "
						+ "(select second_categoryid from second_category "
						+ "where locate(?,name)>0) "
						+ "or locate(?,name)>0 or locate(?,keyword)>0 "
						+ "group by third_categoryid "
						+ "order by onsale_time desc limit ?,?";
				break;
			case 3://热度升序
				sql="select * from third_category "
						+ "where second_categoryid in "
						+ "(select second_categoryid from second_category"
						+ " where first_categoryid in"
						+ "(select first_categoryid from first_category "
						+ "where locate(?,name)>0)) "
						+ "or second_categoryid in "
						+ "(select second_categoryid from second_category "
						+ "where locate(?,name)>0) "
						+ "or locate(?,name)>0 or locate(?,keyword)>0 "
						+ "group by third_categoryid "
						+ "order by evaluate_num limit ?,?";
				break;
			case 4://热度降序
				sql="select * from third_category "
						+ "where second_categoryid in "
						+ "(select second_categoryid from second_category"
						+ " where first_categoryid in"
						+ "(select first_categoryid from first_category "
						+ "where locate(?,name)>0)) "
						+ "or second_categoryid in "
						+ "(select second_categoryid from second_category "
						+ "where locate(?,name)>0) "
						+ "or locate(?,name)>0 or locate(?,keyword)>0 "
						+ "group by third_categoryid "
						+ "order by evaluate_num desc limit ?,?";
				break;
			case 5://价格升序
				sql="select * from third_category "
						+ "where second_categoryid in "
						+ "(select second_categoryid from second_category"
						+ " where first_categoryid in"
						+ "(select first_categoryid from first_category "
						+ "where locate(?,name)>0)) "
						+ "or second_categoryid in "
						+ "(select second_categoryid from second_category "
						+ "where locate(?,name)>0) "
						+ "or locate(?,name)>0 or locate(?,keyword)>0 "
						+ "group by third_categoryid "
						+ "order by price limit ?,?";
				break;
			case 6://价格降序
				sql="select * from third_category "
						+ "where second_categoryid in "
						+ "(select second_categoryid from second_category"
						+ " where first_categoryid in"
						+ "(select first_categoryid from first_category "
						+ "where locate(?,name)>0)) "
						+ "or second_categoryid in "
						+ "(select second_categoryid from second_category "
						+ "where locate(?,name)>0) "
						+ "or locate(?,name)>0 or locate(?,keyword)>0 "
						+ "group by third_categoryid "
						+ "order by price desc limit ?,?";
				break;
			case 0://默认
				sql="select * from third_category "
						+ "where second_categoryid in "
						+ "(select second_categoryid from second_category"
						+ " where first_categoryid in"
						+ "(select first_categoryid from first_category "
						+ "where locate(?,name)>0)) "
						+ "or second_categoryid in "
						+ "(select second_categoryid from second_category "
						+ "where locate(?,name)>0) "
						+ "or locate(?,name)>0 or locate(?,keyword)>0 "
						+ "group by third_categoryid "
						+ "order by onsale_time desc limit ?,?";
				break;
			default:
				sql="select * from third_category "
						+ "where second_categoryid in "
						+ "(select second_categoryid from second_category"
						+ " where first_categoryid in"
						+ "(select first_categoryid from first_category "
						+ "where locate(?,name)>0)) "
						+ "or second_categoryid in "
						+ "(select second_categoryid from second_category "
						+ "where locate(?,name)>0) "
						+ "or locate(?,name)>0 or locate(?,keyword)>0 "
						+ "group by third_categoryid "
						+ "order by onsale_time desc limit ?,?";
		}
		return this.query(sql, new ThirdCategoryRowMapper(),searchWord,searchWord,searchWord, searchWord,offset,pagesize);
	}
	//模糊查询搜索词的所有条数
	public int queryBySearchWordNum(String searchWord){
		String sql="select count(*) from third_category "
				+ "where second_categoryid in "
				+ "(select second_categoryid from second_category"
				+ " where first_categoryid in"
				+ "(select first_categoryid from first_category "
				+ "where locate(?,name)>0)) "
				+ "or second_categoryid in "
				+ "(select second_categoryid from second_category "
				+ "where locate(?,name)>0) "
				+ "or locate(?,name)>0 or locate(?,keyword)>0 ";
		return this.getCount(sql, searchWord,searchWord,searchWord, searchWord);
	}
	//查询选中二级分类id下所有的三级分类商品总记录数,保存在evaluate字段中
	public int listScNum(int selectScid){
		String sql="select count(*) from third_category where second_categoryid=?";
		return this.getCount(sql, selectScid);
	}
	
	//查询选中的三级分类id下的所有二级id对应的三级分类商品总记录数,保存在evaluate字段中
	public int listFcNum(int selectFcid){
		String sql="select count(*) from third_category where "
				+ "second_categoryid in (select "
				+ "second_categoryid from second_category "
				+ "where first_categoryid=?)";
		return this.getCount(sql, selectFcid);
	}
	
	
	//根据排序方式列出选中二级分类id下所有的三级分类商品，如列出所有荣耀系列商品
	public List<ThirdCategory> listSc(int selectScid,int sortWay,int pagesize,int viewPage){
		String sql;
		int offset=pagesize*(viewPage-1);
		switch(sortWay) {
			case 1://上架时间升序
				sql="select * from third_category where second_categoryid=? order by onsale_time limit ?,?";
				break;
			case 2://上架时间降序
				sql="select * from third_category where second_categoryid=? order by onsale_time desc limit ?,?";
				break;
			case 3://热度升序
				sql="select * from third_category where second_categoryid=? order by evaluate_num limit ?,?";
				break;
			case 4://热度降序
				sql="select * from third_category where second_categoryid=? order by evaluate_num desc limit ?,?";
				break;
			case 5://价格升序
				sql="select * from third_category where second_categoryid=? order by price limit ?,?";
				break;
			case 6://价格降序
				sql="select * from third_category where second_categoryid=? order by price desc limit ?,?";
				break;
			case 0://默认
				sql="select * from third_category where second_categoryid=? limit ?,?";
				break;
			default:
				sql="select * from third_category where second_categoryid=? limit ?,?";
		}
		return this.query(sql, new ThirdCategoryRowMapper(), selectScid,offset,pagesize);
	}
	
	//列出选中的三级分类id下的所有二级id对应的三级分类商品，如列出所有手机商品
	public List<ThirdCategory> listFc(int selectFcid,int sortWay,int pagesize,int viewPage){
		String sql="select * from third_category where "
				+ "second_categoryid in (select "
				+ "second_categoryid from second_category "
				+ "where first_categoryid=?) limit ?,?";
		int offset=pagesize*(viewPage-1);
		switch(sortWay) {
		case 1://上架时间升序
			sql="select * from third_category where "
				+ "second_categoryid in (select "
				+ "second_categoryid from second_category "
				+ "where first_categoryid=?) order by onsale_time limit ?,?";
			break;
		case 2://上架时间降序
			sql="select * from third_category where "
				+ "second_categoryid in (select "
				+ "second_categoryid from second_category "
				+ "where first_categoryid=?) order by onsale_time desc limit ?,?";
			break;
		case 3://热度升序
			sql="select * from third_category where "
				+ "second_categoryid in (select "
				+ "second_categoryid from second_category "
				+ "where first_categoryid=?) order by evaluate_num limit ?,?";
			break;
		case 4://热度降序
			sql="select * from third_category where "
					+ "second_categoryid in (select "
					+ "second_categoryid from second_category "
					+ "where first_categoryid=?) order by evaluate_num desc limit ?,?";
			break;
		case 5://价格升序
			sql="select * from third_category where "
					+ "second_categoryid in (select "
					+ "second_categoryid from second_category "
					+ "where first_categoryid=?) order by price limit ?,?";
			break;
		case 6://价格降序
			sql="select * from third_category where "
					+ "second_categoryid in (select "
					+ "second_categoryid from second_category "
					+ "where first_categoryid=?) order by price desc limit ?,?";
			break;
		default:
			sql="select * from third_category where "
					+ "second_categoryid in (select "
					+ "second_categoryid from second_category "
					+ "where first_categoryid=?) limit ?,?";
		}
		
		return this.query(sql, new ThirdCategoryRowMapper(), selectFcid,offset,pagesize);
	}
	public List<TcImg> queryTcImg(int tcid){
		String sql="select * from tc_img where third_categoryid=?";
		return this.query(sql, new TcImgRowMapper(), tcid);
	}
	
	//根据一级分类id在一级分类商品表查询一级分类商品
	public List<FirstCategory> queryByFcid(int fcid){
		String sql="select * from first_category where first_categoryid=?";
		return this.query(sql, new FirstCategoryRowMapper(), fcid);
	}
	
	//根据一级分类id在一级分类商品表查询所有二级分类商品
	public List<SecondCategory> queryScNameByFcid(int fcid){
		String sql="select * from second_category where first_categoryid=?";
		return this.query(sql, new SecondCategoryRowMapper(), fcid);
	}

	//根据二级分类id在二级分类商品表查询二级分类商品
	public List<SecondCategory> queryByScid(int scid){
		String sql="select * from second_category where second_categoryid=?";
		return this.query(sql, new SecondCategoryRowMapper(), scid);
	}
	
	//根据三级分类id在三级分类商品表查询三级分类商品
	public List<ThirdCategory> queryByTcid(int tcid){
		String sql="select * from third_category where third_categoryid=?";
		return this.query(sql, new ThirdCategoryRowMapper(), tcid);
	}
}





